package com.hrt.freemaker;

import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.util.HashMap;

import javax.servlet.http.HttpServletRequest;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.htmlparser.Node;
import org.htmlparser.NodeFilter;
import org.htmlparser.Parser;
import org.htmlparser.filters.TagNameFilter;
import org.htmlparser.tags.TableColumn;
import org.htmlparser.util.NodeList;
import org.htmlparser.util.ParserException;

import com.btcode.web.core.unit.WebContext;

import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateException;

public class ExcelCreater {

    Logger log = Logger.getLogger(ExcelCreater.class.getName());

    private CellStyle cellStyle;

    private HashMap<String, CellStyle> cellStyleCache = new HashMap<>();

    public ExcelCreater(){

    }

    /**
     * 导出excel
     * @param tempPath freemarker模板根目录
     * @param templateName freemarker模板文件名
     * @param data freemarker模板数据
     * @param out 输出流
     * @throws IOException
     * @throws ParserException
     */
    public void exportExcel(String tempPath,String baseExcelField,String templateName,HashMap<String, Object> data,OutputStream out) throws IOException, ParserException {
        ByteArrayOutputStream htmlOut = new ByteArrayOutputStream();
        OutputStreamWriter htmlOutWriter = new OutputStreamWriter(htmlOut);
        exportHTML(tempPath, templateName, data, htmlOutWriter);
        ByteArrayInputStream htmlIn = new ByteArrayInputStream(htmlOut.toByteArray());
        htmlToExecl(htmlIn,out,baseExcelField);
        htmlOut.close();
    }

    /**
     * 使用freemarker生成文件
     * @param templateName
     * @param tempPath
     * @param data
     * @param out
     * @throws IOException
     */
    private void exportHTML(String tempPath,String templateName,HashMap<String, Object> data,Writer out) throws IOException{

        log.info("freeMarker基础目录："+tempPath+",模板名称："+templateName);

        @SuppressWarnings("deprecation")
        Configuration cfg = new Configuration();
        cfg.setDirectoryForTemplateLoading(new File(tempPath));
        Template temp = cfg.getTemplate(templateName, "UTF-8");

        try {
            temp.process(data, out);
        }
        catch (TemplateException e) {
            e.printStackTrace();
        }
        out.flush();
    }

    /**
     * 把html转换成excel
     * @param htmlIn html输入流
     * @param out excel输出流
     * @param baseExcelField 基excel文件，比如文件里面有公式之类的，数据默认填充到第一个工作簿,路径以freeMarkerTemp作为基础路径
     * 			比如/WEB-INF/freeMarkerTemp/monitorData/tableTemp.xls,就写成/monitorData/tableTemp.xls
     * @throws IOException
     * @throws ParserException
     */
    public void htmlToExecl(InputStream htmlIn,OutputStream out,String baseExcelField) throws IOException, ParserException{

        String htmlContext = inputStreamToString(htmlIn);
        Parser htmlParser = new Parser(htmlContext);
        NodeFilter trFilter = new TagNameFilter ("tr");
        NodeList trNodes = htmlParser.extractAllNodesThatMatch(trFilter);
        HSSFWorkbook excelWorkBook = null;
        Sheet excelSheet =null;

        if(baseExcelField == null){
            excelWorkBook = new HSSFWorkbook();
            excelSheet = excelWorkBook.createSheet();
        }
        else{
            HttpServletRequest resq = WebContext.getRequest();
            String tempPath =resq.getServletContext().getRealPath("/") + "WEB-INF/freeMarkerTemp/";
            final InputStream in = new FileInputStream(tempPath+baseExcelField);
            POIFSFileSystem modelbook = new POIFSFileSystem(in);
            excelWorkBook = new HSSFWorkbook(modelbook);
            excelSheet = excelWorkBook.getSheetAt(0);
        }

        //第一列时间列宽度调整
        for(int rowIndex=0;rowIndex<trNodes.size();rowIndex++){

            Node trNode = trNodes.elementAt(rowIndex);
            Row excelRow = excelSheet.createRow(rowIndex);
            NodeFilter tdFilter = new TagNameFilter ("td");
            NodeList tdNodes = new NodeList();
            trNode.collectInto(tdNodes, tdFilter);

            int colIndex = 0;

            for(int j=0;j<tdNodes.size();j++){
                Node tdNode = tdNodes.elementAt(j);
                if(tdNode == null || tdNode.getText().trim().equals("")){
                    continue;
                }
                /**
                 * 如果当前列是属于合并单元格的话，计数器加一，表示跳到下一个单元格
                 */
                while(isMergedRegion(excelSheet, rowIndex, colIndex)){
                    colIndex++;
                }

                TableColumn htmlTdTag = (TableColumn)tdNode;
                createCellFromTd(htmlTdTag, excelRow, rowIndex, colIndex);
                colIndex++;
            }
        }
        try {
            excelWorkBook.write(out);
        }
        catch (IOException e) {
            e.printStackTrace();
        }
        finally {
            try {
                out.close();
            }
            catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 通过html的td创建excel单元格
     * @param htmlTdTag 源html单元格
     * @param excelRow 目标excel行
     * @param rowIndex 要创建单元格的行位置
     * @param colIndex 要创建单元格的列位置
     * @return
     */
    private Cell createCellFromTd(TableColumn htmlTdTag,Row excelRow,int rowIndex,int colIndex){
        String colspanStr = htmlTdTag.getAttribute("colspan");
        String rowspanStr = htmlTdTag.getAttribute("rowspan");

        int colspan = 1;
        int rowspan = 1;

        if(colspanStr != null){
            colspan = Integer.parseInt(colspanStr);
        }
        if(rowspanStr != null){
            rowspan = Integer.parseInt(rowspanStr);
        }

        if(colspan > 1 || rowspan > 1){
            excelRow.getSheet().addMergedRegion(new CellRangeAddress(rowIndex, rowspan+rowIndex-1, colIndex, colspan+colIndex-1));
            for(int i=0;i<rowspan;i++){

                /**
                 * 先判断这一行有没有，如果有就不创建
                 * 解决合并单元格出现值覆盖的问题
                 */
                Row mergedRow = excelRow.getSheet().getRow(rowIndex + i);

                if(mergedRow == null){
                    mergedRow = excelRow.getSheet().createRow(rowIndex + i);
                }

                for(int j=0;j<colspan;j++){
                    Cell mergedCell = mergedRow.createCell(colIndex + j);
                    mergedCell.setCellValue("");
                    styleCellFromTd(htmlTdTag, mergedCell);
                }
            }
        }

        Cell cell = excelRow.createCell(colIndex);
        styleCellFromTd(htmlTdTag, cell);

        Object value = null;

        /**
         * 如果是double类型的，就转成数字格式
         */
        try {
            if(htmlTdTag.toPlainTextString().contains(".")){
                value = Double.valueOf(htmlTdTag.toPlainTextString());
                cell.setCellValue((Double)value);
            }
            else{
                value = htmlTdTag.toPlainTextString();
                cell.setCellValue(htmlTdTag.toPlainTextString());
            }

        }
        catch (Exception e) {
            value = htmlTdTag.toPlainTextString();
            cell.setCellValue(htmlTdTag.toPlainTextString());
        }

        boolean isMergedRegion = isMergedRegion(cell.getSheet(), cell.getRowIndex(), cell.getColumnIndex());
        
        final int i256 = 256;
        
        /**
         * 如果是合并单元格，则不设置宽度
         */
        if(!isMergedRegion){
            int preWidth = cell.getSheet().getColumnWidth(colIndex);
            
            if(value != null){
                int currentWidth = value.toString().getBytes().length * i256;
                if(currentWidth > preWidth){
                    cell.getSheet().setColumnWidth(colIndex, currentWidth);
                }
            }
        }
        
        return cell;
    }


    /**
     * 通过html的td设置excel单元格的样式
     * @param htmlTdTag
     * @param cell
     * @return
     */
    private Cell styleCellFromTd(TableColumn htmlTdTag, Cell cell) {

        String styleStr = htmlTdTag.getAttribute("style");

        if (styleStr == null || styleStr.equals("")) {
            if (cellStyle == null) {
                cellStyle = cell.getSheet().getWorkbook().createCellStyle();
                cellStyle.setAlignment(HorizontalAlignment.CENTER);  // 设置单元格水平方向对其方式
                cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置单元格垂直方向对其方式
                cellStyle.setBorderBottom(BorderStyle.THIN); // 底部边框
                cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 底部边框颜色
                cellStyle.setBorderLeft(BorderStyle.THIN);  // 左边边框
                cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边边框颜色
                cellStyle.setBorderRight(BorderStyle.THIN); // 右边边框
                cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());  // 右边边框颜色
                cellStyle.setBorderTop(BorderStyle.THIN); // 上边边框
                cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());  // 上边边框颜色
            }
            cell.setCellStyle(cellStyle);
            return cell;
        }

        if (cellStyleCache.containsKey(styleStr)) {
            cell.setCellStyle(cellStyleCache.get(styleStr));
            return cell;
        }

        HashMap<String, String> styleInfoMap = new HashMap<>();
        String[] styleInfos = styleStr.split(";");
        for (String style : styleInfos) {
            String[] styleItem = style.split(":");
            if (styleItem.length == 2) {
                styleInfoMap.put(styleItem[0].toUpperCase().trim(), styleItem[1].toUpperCase().trim());
            }
        }

        CellStyle myCellStyle = cell.getSheet().getWorkbook().createCellStyle();
        myCellStyle.setAlignment(HorizontalAlignment.CENTER);  // 设置单元格水平方向对其方式
        myCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置单元格垂直方向对其方式
        myCellStyle.setBorderBottom(BorderStyle.THIN); // 底部边框
        myCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 底部边框颜色
        myCellStyle.setBorderLeft(BorderStyle.THIN);  // 左边边框
        myCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边边框颜色
        myCellStyle.setBorderRight(BorderStyle.THIN); // 右边边框
        myCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());  // 右边边框颜色
        myCellStyle.setBorderTop(BorderStyle.THIN); // 上边边框
        myCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());  // 上边边框颜色

        if (styleInfoMap.containsKey("TEXT-ALIGN")) {
            String value = styleInfoMap.get("TEXT-ALIGN");
            if ("CENTER".equals(value)) {
                myCellStyle.setAlignment(HorizontalAlignment.CENTER);  // 设置单元格水平方向对其方式
            }
            else if ("LEFT".equals(value)) {
                myCellStyle.setAlignment(HorizontalAlignment.LEFT);  // 设置单元格水平方向对其方式
            }
            else if ("RIGHT".equals(value)) {
                myCellStyle.setAlignment(HorizontalAlignment.RIGHT);  // 设置单元格水平方向对其方式
            }
        }

        cellStyleCache.put(styleStr, myCellStyle);

        cell.setCellStyle(myCellStyle);
        return cell;
    }

    /**
     * 检查单元格是否属于合并区
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    private static boolean isMergedRegion(Sheet sheet,int row ,int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if(row >= firstRow && row <= lastRow){
                if(column >= firstColumn && column <= lastColumn){
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * 输入流转成字符串
     * @param htmlIn
     * @return
     * @throws IOException
     */
    private String inputStreamToString(InputStream htmlIn) throws IOException{
        StringBuilder result = new StringBuilder();
        BufferedReader reader = new BufferedReader(new InputStreamReader(htmlIn));
        for(String line=reader.readLine();line != null;line=reader.readLine()){
            result.append(line);
        }
        reader.close();
        return result.toString();
    }
}
